/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.ac.iie.di.ban.dao;

import cn.ac.iie.di.ban.metastore.Load2DBEntity;
import cn.ac.iie.di.ban.tools.MySQLHelper;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.log4j.Logger;

/**
 *
 * @author xiaoliang
 */
public class MySQLDao {

    private static org.apache.log4j.Logger logger = Logger.getLogger(MySQLDao.class.getName());
    private MySQLHelper mySQLHelper;
    private Load2DBEntity load2DBEntity;
    private Connection conn;
    private List<String> dbCols = new ArrayList<>();

    public MySQLDao(MySQLHelper mySQLHelper, Load2DBEntity load2DBEntity) {
        this.mySQLHelper = mySQLHelper;
        this.load2DBEntity = load2DBEntity;
    }

    public void init() {
        while (conn == null) {
            try {
                conn = mySQLHelper.getConnection();
                Thread.sleep(1000);
            } catch (SQLException ex) {
                logger.error(ex.getMessage(), ex);
                mySQLHelper.close(conn);
                conn = null;
            } catch (InterruptedException e) {
            }
        }
    }

    public Map<String, String> saveRecord(Map<String, Object> msg) throws NumberFormatException {
        Map<String, String> result = new HashMap<>();
        try {
            List<String> cols = new ArrayList();
            for (String colString : dbCols) {
                if (msg.get(colString) != null) {
                    cols.add(colString);
                }
            }
            String sql = MySQLHelper.getInsertSql(load2DBEntity.getTable(), cols);
            PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < cols.size(); i++) {
                ps.setObject(i + 1, msg.get(cols.get(i)));
            }
//            sql="INSERT INTO db_metastore.groupp(name) VALUES("+msg.get("name").toString()+")";
//            logger.debug("sql is:~~~~~~~~~~"+sql);
//            conn.prepareStatement(sql);
            int num = ps.executeUpdate();
            
            //get autoIncreseKey
            int autoIncKey=-1;
            ResultSet rs=ps.getGeneratedKeys();
            if(rs.next()){
                autoIncKey=rs.getInt(1);
            }else{
                logger.error("get autoIncreaseKey failed for "+sql);
            }
            result.put("status", "succeeded");
            result.put("detail", "save " + num + " msg,"+"autoIncKey:"+autoIncKey);
            return result;
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
            if (e.getMessage().contains("for key 'PRIMARY'")) {
                result.put("status", "exist");
            } else {
                mySQLHelper.close(conn);
                conn = null;
                init();
                result.put("status", "error");
            }
            result.put("detail", e.getMessage());
            return result;
        }
    }

    /*
    更新方法
     */
    public Map<String, String> updateRecord(Map<String, Object> msg, Map<String, Object> whereMsg) {
        Map<String, String> result = new HashMap<>();
        try {
            List<String> updateCols = new ArrayList<>();//2
            for (String upCol : msg.keySet()) {
                updateCols.add(upCol.toLowerCase());
            }
            List<String> whereCols = new ArrayList();//3
            for (String whereCol : whereMsg.keySet()) {
                whereCols.add(whereCol);
            }
            String sql = MySQLHelper.getUpdateSql(load2DBEntity.getTable(), updateCols, whereCols);
            PreparedStatement ps = conn.prepareStatement(sql);
            for (int i = 0; i < updateCols.size(); i++) {
                ps.setObject(i + 1, msg.get(updateCols.get(i)));
            }
            for (int i = 0; i < whereCols.size(); i++) {
                ps.setObject(i + updateCols.size() + 1, whereMsg.get(whereCols.get(i)));
            }
            int update = ps.executeUpdate();
            if (update > 0) {
                result.put("status", "succeeded");
            } else {
                result.put("status", "notexist");
            }
            result.put("detail", "update " + update + " msg");
            return result;
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            mySQLHelper.close(conn);
            conn = null;
            init();
            result.put("status", "error");
            result.put("detail", ex.getMessage());
            return result;
        }
    }
    
    /*
    删除方法
    */
    public Map<String, String> deleteRecord(Map<String, Object> whereMsg) {
        Map<String, String> result = new HashMap<>();
        try {
            List<String> whereCols = new ArrayList();//3
            for (String whereCol : whereMsg.keySet()) {
                whereCols.add(whereCol);
            }
            String sql = MySQLHelper.getDelSql(load2DBEntity.getTable(),whereCols);
            PreparedStatement ps = conn.prepareStatement(sql);
            for (int i = 0; i < whereCols.size(); i++) {
                ps.setObject(i + 1, whereMsg.get(whereCols.get(i)));
            }
            int delete = ps.executeUpdate();
            if (delete > 0) {
                result.put("status", "succeeded");
            } else {
                result.put("status", "notexist");
            }
            result.put("detail", "delete " + delete + " msg");
            return result;
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            mySQLHelper.close(conn);
            conn = null;
            init();
            result.put("status", "error");
            result.put("detail", ex.getMessage());
            return result;
        }
    }
    public Map<String, Object> findMsgCommon(Set<String> returnCols, Map<String, Object> whereMsg) {
        //select * 操作时，补充*代表的所有字段名
        if(returnCols.isEmpty()){
            returnCols=new HashSet<>(load2DBEntity.getDbCols());
        }
        
        Map<String, Object> result = new HashMap<>();
        List<String> whereCols = new ArrayList();
        List<Map<String, Object>> findMsgs = new ArrayList();
        String sql = "";
        PreparedStatement ps = null;
        Boolean flag = whereMsg.get("gt") == null && whereMsg.get("gte") == null && whereMsg.get("lt") == null && whereMsg.get("lte") == null;
        logger.debug(whereMsg.toString()+flag);
        try {
            if (flag) {
                for (String whereCol : whereMsg.keySet()) {
                    whereCols.add(whereCol.toLowerCase());
                }
                sql = MySQLHelper.getFindSql(load2DBEntity.getTable(), returnCols, whereCols);
                ps = conn.prepareStatement(sql);
                for (int i = 0; i < whereCols.size(); i++) {
                    ps.setObject(i + 1, whereMsg.get(whereCols.get(i)));
                }
            } else {
                sql = MySQLHelper.getFindLGSql(load2DBEntity.getTable(), returnCols, whereMsg);
                ps = conn.prepareStatement(sql);
            }
            logger.debug("sql is :"+sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Map<String, Object> msg = new HashMap<>();
                for (String col : returnCols) {
                    String type = load2DBEntity.getColunmsFromDB().get(col);
                    Object value = null;
                    switch (type) {
                        case "int":
                            value = rs.getInt(col);
                            break;
                        case "varchar":
                            value = rs.getString(col);
                            break;
                        case "bigint":
                            value = rs.getLong(col);
                            break;
                        case "tinyint":
                            value = rs.getInt(col);
                            break;
                        default:
                            logger.error("find msg , db can't find this type and col is " + col);
                            break;
                    }
                    msg.put(col, value);
                }
                if (!msg.isEmpty()) {
                    findMsgs.add(msg);
                }
            }
            if (findMsgs.isEmpty()) {
                result.put("status", "notexist");
            } else {
                result.put("data", findMsgs);
                result.put("status", "succeeded");
            }
            result.put("detail", "find " + findMsgs.size() + " msg");
            return result;
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            mySQLHelper.close(conn);
            conn = null;
            init();
            result.put("data", "");
            result.put("status", "error");
            result.put("detail", ex.getMessage());
            return result;
        }
    }


    /*
    获取数据库的表结构
     */
    public void getDBCols() throws SQLException {
        Map<String, String> colunmsFromDB = new HashMap<>();
        String sql = MySQLHelper.getColumns(load2DBEntity.getTable());
        Statement stat = conn.createStatement();
        ResultSet rs1 = stat.executeQuery(sql);
        String col = "";
        String type = "";
        while (rs1.next()) {
            for (int i = 1; i < 3; i++) {
                if (i % 2 == 0) {
                    type = rs1.getString(i);
                    type = type.split("[(]")[0];
                } else {
                    col = rs1.getString(i);
                    dbCols.add(col);
                }
            }
            colunmsFromDB.put(col, type);
        }
        load2DBEntity.setColunmsFromDB(colunmsFromDB);
        load2DBEntity.setDbCols(dbCols);
    }

//    {
    //                String type = load2DBEntity.getColunmsFromDB().get(cols.get(i));
//                switch (type) {
//                    case "int":
//                        ps.setInt(i + 1, Integer.parseInt(msg.get(cols.get(i)).toString()));
//                        break;
//                    case "varchar":
//                        ps.setString(i + 1, msg.get(cols.get(i)).toString());
//                        break;
//                    case "bigint":
//                        ps.setLong(i + 1, Long.parseLong(msg.get(cols.get(i)).toString()));
//                        break;
//                    case "tinyint":
//                        ps.setInt(i + 1, Integer.parseInt(msg.get(cols.get(i)).toString()));
//                        break;
//                    default:
//                        logger.error("db can't find this type and value is "+ msg.get(cols.get(i)).toString());
//                        break;
//                }
//    }
    public Connection getConn() {
        return conn;
    }
}
